package com.is.control;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.is.dbconn.DBConnect;
import com.is.model.ParamClass;
import com.is.model.TeacherAssess;

public class ParamClassControl {
	public static boolean insertParamClass(ParamClass pc){
		DBConnect dbConnect =new DBConnect();
		String sql = "insert into param_class (grade,className,stuyears) values(?,?,?)";
		dbConnect.prepareStatement(sql);
		boolean flag=false;
		try {
			dbConnect.setString(1, pc.getGrade());
			dbConnect.setString(2, pc.getClassName());
			dbConnect.setString(3, pc.getStuyears());
			if(dbConnect.executeUpdate()){
				flag=true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("insertParamClass Error");
		}finally{
			dbConnect.close();
		}
		return flag;
	}
/**
 * 
 * @param id
 * @return
 */
	public static boolean deleteParamClass(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from param_class where id=" + id;
		System.out.println("sql:" + sql);
		dbc.prepareStatement(sql);
		try{
			if(dbc.executeUpdate()){
				return true;
			}
		} catch(Exception e){
			e.printStackTrace();
			System.out.println("deleteParamClass ERROR");
		} finally{
			dbc.close();
		}
		return false;
	}
/**
 * 
 * @param id
 * @param className
 * @return
 */
	public static boolean updateParamClass(int id, String className){
		DBConnect dbc = new DBConnect();
		String sql = "update param_class set className = ? where id = " + id;
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1, className);
			if(dbc.executeUpdate()){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("updateParamClass error");
		} finally{
			dbc.close();
		}
		return false;
	}
	public static String getClassName(int id){
		DBConnect dbConnect =new DBConnect();
		String sql = "select className from param_class where id = '"+id+"'";
		ResultSet rs = null;
		dbConnect.prepareStatement(sql);
		try {
			rs = dbConnect.executeQuery();
			if(rs.next()){
				return rs.getString("className");
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getClassName Error");
		}finally{
			dbConnect.close();
		}
		return "";
	}
	
	
	public static List<ParamClass> getParamClassesByStuyear(int stuyearId) {
		List<ParamClass> pcs = new ArrayList<ParamClass>();
 		DBConnect dbConnect = new DBConnect();
		String sql = "select * from param_class where stuyears like '%@"+stuyearId+"@%'";
		dbConnect.prepareStatement(sql);
		ResultSet rs=null;
		try {
			rs= dbConnect.executeQuery();
			while(rs.next()){
				ParamClass pc = new ParamClass();
				pc.setClassName(rs.getString("className"));
				pc.setId(rs.getInt("id"));
				pc.setGrade(rs.getString("grade"));
				pc.setStuyears(rs.getString("stuyears"));
				pcs.add(pc);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getParamClassesByStuyearId ERROR");
		}finally{
			dbConnect.close();
		}
		return pcs;
	}
	
	public static boolean CopyClassess(int fromStuyear,int toStuyear) {
		boolean flag=false;
		List<ParamClass> pcs = getParamClassesByStuyear(fromStuyear); 
		DBConnect dbc = new DBConnect();
		PreparedStatement pstmt =null;
		String sql = "update param_class set stuyears = concat(stuyears,'@"+toStuyear+"@') where id=?";
		dbc.setAutoCommit(false);
		dbc.prepareStatement(sql);
		pstmt = dbc.getPreparedStatement();
		try {
			for(ParamClass pc:pcs){
				int index=1;
				pstmt.setInt(1, pc.getId());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			dbc.commit();
			dbc.setAutoCommit(true);
			flag= true;
	}catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
	}finally{
		dbc.close();
	}
	return flag;
	}

	public static String[]  getMyStuyearsByClassId(String classId){
		DBConnect dbc = new DBConnect();
		String sql = "select stuyears from param_class where id='"+classId+"'";
		dbc.prepareStatement(sql);
		ResultSet rs = null;
		try {
			rs = dbc.executeQuery();
			while(rs.next()){
				if(rs.getString("stuyears")!=null){
					String[] a = rs.getString("stuyears").trim().split("@");
					return a;
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getMyStuyearsByClassNameAndGrade ERROR");
		}finally{
			dbc.close();
		}
		return null;
	}
//	public static void main(String args[])  {
//		System.out.println(getParamClassesByGrade("2012").size());
//		
//	}
}